package com.ermax.aircond.report.service;

import java.awt.BasicStroke;
import java.awt.image.BufferedImage;
import java.io.IOException;
import java.io.Serializable;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Locale;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFPrintSetup;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Font;
import org.hibernate.envers.RevisionType;
import org.jfree.chart.ChartFactory;
import org.jfree.chart.JFreeChart;
import org.jfree.chart.axis.DateAxis;
import org.jfree.chart.axis.DateTickUnit;
import org.jfree.chart.axis.DateTickUnitType;
import org.jfree.chart.axis.NumberAxis;
import org.jfree.chart.plot.XYPlot;
import org.jfree.data.time.Day;
import org.jfree.data.time.Hour;
import org.jfree.data.time.RegularTimePeriod;
import org.jfree.data.time.TimeSeries;
import org.jfree.data.time.TimeSeriesCollection;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import com.ermax.aircond.common.domain.ReadMachineDataAudit;
import com.ermax.aircond.common.domain.UserDetail;
import com.ermax.aircond.common.domain.WriteMachineData;
import com.ermax.aircond.common.service.ReadMachineDataService;
import com.ermax.aircond.maintain.domain.Machine;
import com.ermax.aircond.maintain.service.MaintainService;
import com.ermax.aircond.report.dao.AuditLogDao;
import com.ermax.aircond.report.domain.CommandCheckDisplay;
import com.ermax.aircond.report.domain.ErrorDetail;
import com.ermax.aircond.resource.MessageBundle;
import com.ermax.aircond.util.domain.AuditedRevEntity;
import com.ermax.aircond.util.service.CalendarService;
import com.ermax.aircond.util.service.MachineCommandService;

@Service("auditLogService")
public class AuditLogService implements java.io.Serializable {

	/**
	 * 
	 */
	private static final long serialVersionUID = 1L;
	private static final Logger logger = LoggerFactory.getLogger(AuditLogService.class);
	private static final DateFormat FULL_DATA_FORMAT = new SimpleDateFormat("dd/MM/yyyy HH:mm:ss");
	private static final DateFormat DATA_FORMAT = new SimpleDateFormat("dd/MM/yyyy");
	private static final int EXCEL_WIDTH_RATE = 300;		

	@Resource(name = "auditLogDao")
	private AuditLogDao auditLogDao;

	@Resource(name = "maintainService")
	private MaintainService maintainService;

	@Resource(name = "machineCommandService")
	private MachineCommandService machineCommandService;

	@Resource(name = "calendarService")
	private CalendarService calendarService;

	@Resource(name = "readMachineDataService")
	private ReadMachineDataService readMachineDataService;
	
	@SuppressWarnings("unchecked")
	public <T> T getDomainByVersion(Class<?> clazz, final Serializable primaryKey, final int versionId) {
		return (T) auditLogDao.getDomainByVersion(clazz, primaryKey, versionId);
	}

	public List<Number> getRevNumbers(Class<?> clazz, final Serializable primaryKey) {
		return auditLogDao.getRevNumbers(clazz, primaryKey);
	}

	public List<AuditedRevEntity> getRevEntities(Class<?> clazz, Date fromDate, Date toDate, long auditedByUserId) {
		String auditedByUserName = null;
		if (auditedByUserId > 0) {
			UserDetail userDetail;
			try {
				userDetail = maintainService.getUserDetailById(auditedByUserId);
				if (userDetail != null) {
					auditedByUserName = userDetail.getUsername();
				}
			} catch (Exception e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}

		}

		List<AuditedRevEntity> revEntities = new ArrayList<AuditedRevEntity>();

		List<Object> results = auditLogDao.getRevEntities(clazz, fromDate, toDate, auditedByUserName);
		for (int i = 0; i < results.size(); i++) {
			Object[] objArray = (Object[]) results.get(i);
			AuditedRevEntity auditedRevEntity = (AuditedRevEntity) objArray[1];
			revEntities.add(auditedRevEntity);
		}

		return revEntities;
	}

	public HSSFWorkbook getMDataExcelReport(List<Machine> machineList, String searchCriteria, Date fromDate, Date toDate, long auditUserId, Locale locale) {
		HSSFWorkbook workbook = new HSSFWorkbook();

		fromDate = calendarService.convertStartOftheDate(fromDate);
		toDate = calendarService.convertEndOftheDate(toDate);

		String auditUsername = null;
		if (auditUserId > 0) {
			UserDetail userDetail;
			try {
				userDetail = maintainService.getUserDetailById(auditUserId);
				if (userDetail != null) {
					auditUsername = userDetail.getUsername();
				}
			} catch (Exception e) {
			}

		}

		try {
			HSSFSheet sheet = workbook.createSheet();
			int startRow = 0;

			// Row 1
			HSSFRow machineRow = sheet.createRow(startRow++);

			// Row 1 - Cell 0
			HSSFCell machineCell0 = machineRow.createCell(0);
			machineCell0.setCellValue(MessageBundle.getInstance().getMessage("search_criteria", locale));

			// Row 1 - Cell 1
			HSSFCell machineCell1 = machineRow.createCell(1);
			machineCell1.setCellValue(searchCriteria);

			// Row 2
			HSSFRow fromDateRow = sheet.createRow(startRow++);
			HSSFCell fromDateCell0 = fromDateRow.createCell(0);
			fromDateCell0.setCellValue(MessageBundle.getInstance().getMessage("from_date", locale));
			HSSFCell fromDateCell1 = fromDateRow.createCell(1);
			fromDateCell1.setCellValue(DATA_FORMAT.format(fromDate));

			// Row 3
			HSSFRow toDateRow = sheet.createRow(startRow++);
			HSSFCell toDateCell0 = toDateRow.createCell(0);
			toDateCell0.setCellValue(MessageBundle.getInstance().getMessage("to_date", locale));
			HSSFCell toDateCell1 = toDateRow.createCell(1);
			toDateCell1.setCellValue(DATA_FORMAT.format(toDate));

			// Row4
			if (auditUsername != null) {
				HSSFRow userRow = sheet.createRow(startRow++);
				HSSFCell userCell0 = userRow.createCell(0);
				userCell0.setCellValue(MessageBundle.getInstance().getMessage("audited_by", locale));
				HSSFCell userCell1 = userRow.createCell(1);
				userCell1.setCellValue(auditUsername);
			}

			// Create Space row
			HSSFRow spaceRow1 = sheet.createRow(startRow++);
			HSSFCell spaceCell0 = spaceRow1.createCell(0);
			spaceCell0.setCellValue("");

			// Title
			HSSFRow titleRow = sheet.createRow(startRow++);

			HSSFCellStyle titleStyle = workbook.createCellStyle();
			titleStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
			HSSFFont titleFont = workbook.createFont();
			titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
			titleStyle.setFont(titleFont);

			HSSFCell versionCell = titleRow.createCell(0);
			versionCell.setCellStyle(titleStyle);
			versionCell.setCellValue(MessageBundle.getInstance().getMessage("version_id", locale));

			HSSFCell timeCell = titleRow.createCell(1);
			timeCell.setCellStyle(titleStyle);
			timeCell.setCellValue(MessageBundle.getInstance().getMessage("time", locale));

			HSSFCell entityIdCell = titleRow.createCell(2);
			entityIdCell.setCellStyle(titleStyle);
			entityIdCell.setCellValue(MessageBundle.getInstance().getMessage("machine", locale));

			HSSFCell revTypeCell = titleRow.createCell(3);
			revTypeCell.setCellStyle(titleStyle);
			revTypeCell.setCellValue(MessageBundle.getInstance().getMessage("action", locale));

			HSSFCell startCell = titleRow.createCell(4);
			startCell.setCellStyle(titleStyle);
			startCell.setCellValue(MessageBundle.getInstance().getMessage("start_option", locale));

			HSSFCell tempCell = titleRow.createCell(5);
			tempCell.setCellStyle(titleStyle);
			StringBuilder sbTemp = new StringBuilder();
			sbTemp.append(MessageBundle.getInstance().getMessage("temperature", locale));
			sbTemp.append("(");
			sbTemp.append(MessageBundle.getInstance().getMessage("celsius", locale));
			sbTemp.append(")");
			tempCell.setCellValue(sbTemp.toString());

			HSSFCell minTempCell = titleRow.createCell(6);
			minTempCell.setCellStyle(titleStyle);
			StringBuilder sbMinTemp = new StringBuilder();
			sbMinTemp.append(MessageBundle.getInstance().getMessage("minimum_temperature", locale));
			sbMinTemp.append("(");
			sbMinTemp.append(MessageBundle.getInstance().getMessage("celsius", locale));
			sbMinTemp.append(")");
			minTempCell.setCellValue(sbMinTemp.toString());

			HSSFCell fanSpeedCell = titleRow.createCell(7);
			fanSpeedCell.setCellStyle(titleStyle);
			fanSpeedCell.setCellValue(MessageBundle.getInstance().getMessage("fan_speed", locale));

			HSSFCell keyLockCell = titleRow.createCell(8);
			keyLockCell.setCellStyle(titleStyle);
			keyLockCell.setCellValue(MessageBundle.getInstance().getMessage("key_lock", locale));

			HSSFCell errorMessageCell = titleRow.createCell(9);
			errorMessageCell.setCellStyle(titleStyle);
			errorMessageCell.setCellValue(MessageBundle.getInstance().getMessage("error_message", locale));

			Iterator<Machine> iter = machineList.iterator();
			while (iter.hasNext()) {

				Machine machine = iter.next();
				short nid = machineCommandService.getShortNid(machine.getGateNo(), machine.getDeviceNo());

				List<Object> results = auditLogDao.getRevEntities(WriteMachineData.class, nid, fromDate, toDate, auditUsername);

				writeMDataToWorkbook(sheet, startRow, machine, results, locale);

				startRow += results.size();

			}

			sheet.setColumnWidth(0, (int) (10 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(1, (int) (18 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(2, (int) (20 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(3, (int) (12 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(4, (int) (12 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(5, (int) (20 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(6, (int) (20 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(7, (int) (12 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(8, (int) (12 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(9, (int) (12 * EXCEL_WIDTH_RATE));


			HSSFPrintSetup printSetup = sheet.getPrintSetup();
			sheet.setAutobreaks(true);

			printSetup.setFitHeight((short) 1);
			printSetup.setFitWidth((short) 1);

		} catch (Exception e) {
			logger.error(e.getMessage());
		}

		return workbook;
	}

	private void writeMDataToWorkbook(HSSFSheet sheet, int rowStart, Machine machine, List<Object> results, Locale locale) {

		int rowSize = rowStart;

		for (int i = 0; i < results.size(); i++) {
			rowSize++;

			Object[] objArray = (Object[]) results.get(i);

			HSSFRow row = sheet.createRow(i + rowStart);

			WriteMachineData data = (WriteMachineData) objArray[0];
			AuditedRevEntity revEntity = (AuditedRevEntity) objArray[1];
			RevisionType type = (RevisionType) objArray[2];

			// Cell 0 version id
			HSSFCell cell0 = row.createCell(0);
			cell0.setCellValue(String.valueOf(revEntity.getId()));

			// Cell 1 Version timestamp
			HSSFCell cell1 = row.createCell(1);
			cell1.setCellValue(FULL_DATA_FORMAT.format(new Date(revEntity.getTimestamp())));

			// cell3 entity id
			HSSFCell cell3 = row.createCell(2);
			//short nid = machineCommandService.getShortNid(machine.getGateNo(), machine.getDeviceNo());
			cell3.setCellValue(machine.getMachineName());

			// Cell 4 RevisionType
			HSSFCell cell4 = row.createCell(3);
			if (RevisionType.ADD == type) {
				cell4.setCellValue(MessageBundle.getInstance().getMessage("create", locale));
			} else if (RevisionType.MOD == type) {
				cell4.setCellValue(MessageBundle.getInstance().getMessage("edit", locale));
			} else if (RevisionType.DEL == type) {
				cell4.setCellValue(MessageBundle.getInstance().getMessage("delete", locale));
			}

			List<String> commands = machineCommandService.extractMachineData(data.getCmd());

			// Cell 6 On/Off
			HSSFCell cell6 = row.createCell(4);
			String startValue = machineCommandService.getOnOffValue(commands);
			if (startValue != null) {
				if ("1".equals(startValue)) {
					cell6.setCellValue(MessageBundle.getInstance().getMessage("start_on", locale));
				} else {
					cell6.setCellValue(MessageBundle.getInstance().getMessage("start_off", locale));
				}
			}

			// Cell 7 default temp
			HSSFCell cell7 = row.createCell(5);
			String temp = machineCommandService.getDefaultTemperatureValue(commands);
			if (temp != null) {
				cell7.setCellValue(temp);
			}

			// Cell 8 min temp
			HSSFCell cell8 = row.createCell(6);
			String minTemp = machineCommandService.getMinimumTemperatureValue(commands);
			if (minTemp != null) {
				cell8.setCellValue(minTemp);
			}

			// Cell 9 fan speed
			HSSFCell cell9 = row.createCell(7);
			String fanSpeed = machineCommandService.getFanSpeedValue(commands);
			if (fanSpeed != null) {
				if ("0".equals(fanSpeed)) {
					cell9.setCellValue(MessageBundle.getInstance().getMessage("fan_speed_auto", locale));
				} else if ("1".equals(fanSpeed)) {
					cell9.setCellValue(MessageBundle.getInstance().getMessage("fan_speed_high", locale));
				} else if ("2".equals(fanSpeed)) {
					cell9.setCellValue(MessageBundle.getInstance().getMessage("fan_speed_mid", locale));
				} else if ("3".equals(fanSpeed)) {
					cell9.setCellValue(MessageBundle.getInstance().getMessage("fan_speed_low", locale));
				}
			}

			// Cell 10 Key lock
			HSSFCell cell10 = row.createCell(8);
			String keyLock = machineCommandService.getKeyLockValue(commands);
			if (keyLock != null) {
				if ("0".equals(keyLock)) {
					cell10.setCellValue(MessageBundle.getInstance().getMessage("key_lock_no_lock", locale));
				} else if ("1".equals(keyLock)) {
					cell10.setCellValue(MessageBundle.getInstance().getMessage("key_lock_fan_speed_only", locale));
				}
			}

			// Cell 11 Error message
			HSSFCell cell11 = row.createCell(9);
			String errorMessage = machineCommandService.getErrorMessageValue(commands);
			if (errorMessage != null) {
				cell11.setCellValue(errorMessage);
			}

			

			if (rowSize > 60000) {
				HSSFRow endRow = sheet.createRow(i + rowStart + 2);
				HSSFCell endCell = endRow.createCell(0);
				endCell.setCellValue("Records size are too large....");
				break;
			}
		}

	}
	
	

	public HSSFWorkbook getSDataExcelReport(List<Machine> machineList, String searchCriteria, Date fromDate, Date toDate, Locale locale) {
		HSSFWorkbook workbook = new HSSFWorkbook();

		fromDate = calendarService.convertStartOftheDate(fromDate);
		toDate = calendarService.convertEndOftheDate(toDate);

		try {
			HSSFSheet sheet = workbook.createSheet();
			int startRow = 0;

			// Row 1
			HSSFRow machineRow = sheet.createRow(startRow++);

			// Row 1 - Cell 0
			HSSFCell machineCell0 = machineRow.createCell(0);
			machineCell0.setCellValue(MessageBundle.getInstance().getMessage("search_criteria", locale));

			// Row 1 - Cell 1
			HSSFCell machineCell1 = machineRow.createCell(1);
			machineCell1.setCellValue(searchCriteria);

			// Row 2
			HSSFRow fromDateRow = sheet.createRow(startRow++);
			HSSFCell fromDateCell0 = fromDateRow.createCell(0);
			fromDateCell0.setCellValue(MessageBundle.getInstance().getMessage("from_date", locale));
			HSSFCell fromDateCell1 = fromDateRow.createCell(1);
			fromDateCell1.setCellValue(DATA_FORMAT.format(fromDate));

			// Row 3
			HSSFRow toDateRow = sheet.createRow(startRow++);
			HSSFCell toDateCell0 = toDateRow.createCell(0);
			toDateCell0.setCellValue(MessageBundle.getInstance().getMessage("to_date", locale));
			HSSFCell toDateCell1 = toDateRow.createCell(1);
			toDateCell1.setCellValue(DATA_FORMAT.format(toDate));

			// Create Space row
			HSSFRow spaceRow1 = sheet.createRow(startRow++);
			HSSFCell spaceCell0 = spaceRow1.createCell(0);
			spaceCell0.setCellValue("");

			// Title
			HSSFRow titleRow = sheet.createRow(startRow++);

			HSSFCellStyle titleStyle = workbook.createCellStyle();
			titleStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
			HSSFFont titleFont = workbook.createFont();
			titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
			titleStyle.setFont(titleFont);

			int col = 0;
				
			HSSFCell entityIdCell = titleRow.createCell(col++);
			entityIdCell.setCellStyle(titleStyle);			
			entityIdCell.setCellValue(MessageBundle.getInstance().getMessage("machine", locale));

			HSSFCell revTypeCell = titleRow.createCell(col++);
			revTypeCell.setCellStyle(titleStyle);
			revTypeCell.setCellValue(MessageBundle.getInstance().getMessage("time", locale));

			
			HSSFCell tempCell = titleRow.createCell(col++);
			tempCell.setCellStyle(titleStyle);
			StringBuilder sbTemp = new StringBuilder();
			sbTemp.append(MessageBundle.getInstance().getMessage("temperature", locale));
			sbTemp.append("(");
			sbTemp.append(MessageBundle.getInstance().getMessage("celsius", locale));
			sbTemp.append(")");
			tempCell.setCellValue(sbTemp.toString());

			HSSFCell minTempCell = titleRow.createCell(col++);
			minTempCell.setCellStyle(titleStyle);
			StringBuilder sbMinTemp = new StringBuilder();
			sbMinTemp.append(MessageBundle.getInstance().getMessage("minimum_temperature", locale));
			sbMinTemp.append("(");
			sbMinTemp.append(MessageBundle.getInstance().getMessage("celsius", locale));
			sbMinTemp.append(")");
			minTempCell.setCellValue(sbMinTemp.toString());
			
			HSSFCell roomTempCell = titleRow.createCell(col++);
			roomTempCell.setCellStyle(titleStyle);
			StringBuilder sbRoomTemp = new StringBuilder();
			sbRoomTemp.append(MessageBundle.getInstance().getMessage("room_temperature", locale));
			sbRoomTemp.append("(");
			sbRoomTemp.append(MessageBundle.getInstance().getMessage("celsius", locale));
			sbRoomTemp.append(")");
			roomTempCell.setCellValue(sbRoomTemp.toString());

			int rowSize = startRow;
			Iterator<Machine> iter = machineList.iterator();
			
			back :{
				while (iter.hasNext()) {
					
					
					Machine machine = iter.next();
					short nid = machineCommandService.getShortNid(machine.getGateNo(), machine.getDeviceNo());
	
					List<ReadMachineDataAudit> audits = readMachineDataService.getReadMachineDataAudits(nid, fromDate, toDate); 
	
					Iterator<ReadMachineDataAudit> auditIter = audits.iterator();
					while(auditIter.hasNext()){
						rowSize++;
						
						ReadMachineDataAudit dataAudit = auditIter.next();
						List<String> commands = machineCommandService.extractMachineData(dataAudit.getData());
	
						
						HSSFRow row = sheet.createRow(startRow++);
																
						//machine name					
						HSSFCell cell0 = row.createCell(0);
						cell0.setCellValue(machine.getMachineName());
						
						//time
						HSSFCell cell1 = row.createCell(1);
						cell1.setCellValue(FULL_DATA_FORMAT.format(dataAudit.getUpdateDate()));
											
						HSSFCell cell2 = row.createCell(2);
						String temp = machineCommandService.getDefaultTemperatureValue(commands);
						if (temp != null) {
							cell2.setCellValue(temp);
						}
						
						HSSFCell cell3 = row.createCell(3);
						String minTemp = machineCommandService.getMinimumTemperatureValue(commands);
						if (minTemp != null) {
							cell3.setCellValue(minTemp);
						}
						
						HSSFCell cell4 = row.createCell(4);
						String roomTemp = machineCommandService.getRoomTemperatureValue(commands);
						if (minTemp != null) {
							cell4.setCellValue(roomTemp);
						}
						
						if (rowSize > 60000) {
							HSSFRow endRow = sheet.createRow(rowSize + 2);
							HSSFCell endCell = endRow.createCell(0);
							endCell.setCellValue("Records size are too large....");
							break back;
						}
										
					}
					
				}
				

			}

			sheet.setColumnWidth(0, (int) (20 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(1, (int) (30 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(2, (int) (20 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(3, (int) (20 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(4, (int) (20 * EXCEL_WIDTH_RATE));
			
			
			HSSFPrintSetup printSetup = sheet.getPrintSetup();
			sheet.setAutobreaks(true);

			printSetup.setFitHeight((short) 1);
			printSetup.setFitWidth((short) 1);

		} catch (Exception e) {
			logger.error(e.getMessage());
		}

		return workbook;
	}
	
	public HSSFWorkbook getSDataExcelSummaryReport(List<Machine> machineList, String searchCriteria, Date fromDate, Date toDate, Locale locale) {
		HSSFWorkbook workbook = new HSSFWorkbook();

		fromDate = calendarService.convertStartOftheDate(fromDate);
		toDate = calendarService.convertEndOftheDate(toDate);

		try {
			HSSFSheet sheet = workbook.createSheet();
			int startRow = 0;

			// Row 1
			HSSFRow machineRow = sheet.createRow(startRow++);

			// Row 1 - Cell 0
			HSSFCell machineCell0 = machineRow.createCell(0);
			machineCell0.setCellValue(MessageBundle.getInstance().getMessage("search_criteria", locale));

			// Row 1 - Cell 1
			HSSFCell machineCell1 = machineRow.createCell(1);
			machineCell1.setCellValue(searchCriteria);

			// Row 2
			HSSFRow fromDateRow = sheet.createRow(startRow++);
			HSSFCell fromDateCell0 = fromDateRow.createCell(0);
			fromDateCell0.setCellValue(MessageBundle.getInstance().getMessage("from_date", locale));
			HSSFCell fromDateCell1 = fromDateRow.createCell(1);
			fromDateCell1.setCellValue(DATA_FORMAT.format(fromDate));

			// Row 3
			HSSFRow toDateRow = sheet.createRow(startRow++);
			HSSFCell toDateCell0 = toDateRow.createCell(0);
			toDateCell0.setCellValue(MessageBundle.getInstance().getMessage("to_date", locale));
			HSSFCell toDateCell1 = toDateRow.createCell(1);
			toDateCell1.setCellValue(DATA_FORMAT.format(toDate));

			// Create Space row
			HSSFRow spaceRow1 = sheet.createRow(startRow++);
			HSSFCell spaceCell0 = spaceRow1.createCell(0);
			spaceCell0.setCellValue("");

			// Title
			HSSFRow titleRow = sheet.createRow(startRow++);

			HSSFCellStyle titleStyle = workbook.createCellStyle();
			titleStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
			HSSFFont titleFont = workbook.createFont();
			titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
			titleStyle.setFont(titleFont);

			int col = 0;
				
			HSSFCell entityIdCell = titleRow.createCell(col++);
			entityIdCell.setCellStyle(titleStyle);			
			entityIdCell.setCellValue(MessageBundle.getInstance().getMessage("machine", locale));

			HSSFCell revTypeCell = titleRow.createCell(col++);
			revTypeCell.setCellStyle(titleStyle);
			revTypeCell.setCellValue(MessageBundle.getInstance().getMessage("time", locale));

			
			HSSFCell tempCell = titleRow.createCell(col++);
			tempCell.setCellStyle(titleStyle);
			StringBuilder sbTemp = new StringBuilder();
			sbTemp.append(MessageBundle.getInstance().getMessage("temperature", locale));
			sbTemp.append("(");
			sbTemp.append(MessageBundle.getInstance().getMessage("celsius", locale));
			sbTemp.append(")");
			tempCell.setCellValue(sbTemp.toString());

			HSSFCell minTempCell = titleRow.createCell(col++);
			minTempCell.setCellStyle(titleStyle);
			StringBuilder sbMinTemp = new StringBuilder();
			sbMinTemp.append(MessageBundle.getInstance().getMessage("minimum_temperature", locale));
			sbMinTemp.append("(");
			sbMinTemp.append(MessageBundle.getInstance().getMessage("celsius", locale));
			sbMinTemp.append(")");
			minTempCell.setCellValue(sbMinTemp.toString());
			
			HSSFCell roomTempCell = titleRow.createCell(col++);
			roomTempCell.setCellStyle(titleStyle);
			StringBuilder sbRoomTemp = new StringBuilder();
			sbRoomTemp.append(MessageBundle.getInstance().getMessage("room_temperature", locale));
			sbRoomTemp.append("(");
			sbRoomTemp.append(MessageBundle.getInstance().getMessage("celsius", locale));
			sbRoomTemp.append(")");
			roomTempCell.setCellValue(sbRoomTemp.toString());
			
			Iterator<Machine> iter = machineList.iterator();
			
			back :{
				while (iter.hasNext()) {
					
					
					Machine machine = iter.next();
					short nid = machineCommandService.getShortNid(machine.getGateNo(), machine.getDeviceNo());
					
					int intervalType = readMachineDataService.getIntervalType(fromDate, toDate);
					List<Object[]> audits = null;
					
					if(intervalType == ReadMachineDataService.INTERVAL_TYPE_1DAY){
						audits = readMachineDataService.getReadMachineDataAuditsGroupByDay(nid, fromDate, toDate);					
					}else if(intervalType == ReadMachineDataService.INTERVAL_TYPE_6HOUR){
						audits = readMachineDataService.getReadMachineDataAuditsGroupBySixHour(nid, fromDate, toDate);
					}else{
						audits = readMachineDataService.getReadMachineDataAuditsGroupByHour(nid, fromDate, toDate);
					}
					
					for (int i = 0; i < audits.size(); i++) {
						

						Object[] objects = audits.get(i);
						
						Date updateDate = null;
						double defaultTemp = 0;
						double roomTemp = 0;
						double minTemp = 0;
						try{
							int year = (Integer) objects[0];
							int month = (Integer) objects[1];
							int day = (Integer) objects[2];
							int hour = (Integer) objects[3];
							updateDate = calendarService.convertDate(year, month, day, hour);					
							defaultTemp = (Double) objects[4];
							roomTemp = (Double) objects[5];
							minTemp = (Double) objects[6];
						}catch(Exception ex){
							continue;
						}
						
						if(updateDate == null){
							continue;
						}		
						
						HSSFRow row = sheet.createRow(startRow++);
						
						//machine name					
						HSSFCell cell0 = row.createCell(0);
						cell0.setCellValue(machine.getMachineName());
						
						//time
						HSSFCell cell1 = row.createCell(1);
						cell1.setCellValue(FULL_DATA_FORMAT.format(updateDate));
						
						HSSFCell cell2 = row.createCell(2);						
						cell2.setCellValue(String.valueOf(defaultTemp));						
						
						HSSFCell cell3 = row.createCell(3);						
						cell3.setCellValue(String.valueOf(roomTemp));
						
						HSSFCell cell4 = row.createCell(4);						
						cell4.setCellValue(String.valueOf(minTemp));
						
						if (startRow > 60000) {
							HSSFRow endRow = sheet.createRow(startRow + 2);
							HSSFCell endCell = endRow.createCell(0);
							endCell.setCellValue("Records size are too large....");
							break back;
						}
						
					}																																																		
																					
					
				}
				

			}

			sheet.setColumnWidth(0, (int) (20 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(1, (int) (30 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(2, (int) (20 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(3, (int) (20 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(4, (int) (20 * EXCEL_WIDTH_RATE));
			
			
			HSSFPrintSetup printSetup = sheet.getPrintSetup();
			sheet.setAutobreaks(true);

			printSetup.setFitHeight((short) 1);
			printSetup.setFitWidth((short) 1);

		} catch (Exception e) {
			logger.error(e.getMessage());
		}

		return workbook;
	}
	
	public BufferedImage getChartBufferedImage(long machineId, Date searchFromDate, Date searchToDate, Locale locale) throws IOException {

		try {
			
			Machine machine = maintainService.getMachineById(machineId);
			short nid = machineCommandService.getShortNid(machine.getGateNo(), machine.getDeviceNo());
			
			int intervalType = readMachineDataService.getIntervalType(searchFromDate, searchToDate);
			List<Object[]> audits = null;
			
			if(intervalType == ReadMachineDataService.INTERVAL_TYPE_1DAY){
				audits = readMachineDataService.getReadMachineDataAuditsGroupByDay(nid, searchFromDate, searchToDate);					
			}else if(intervalType == ReadMachineDataService.INTERVAL_TYPE_6HOUR){
				audits = readMachineDataService.getReadMachineDataAuditsGroupBySixHour(nid, searchFromDate, searchToDate);
			}else{
				audits = readMachineDataService.getReadMachineDataAuditsGroupByHour(nid, searchFromDate, searchToDate);
			}
						
														
			final TimeSeries s1 = new TimeSeries("Set Temp");
			//Map<String, List<Double>> cacheMapS1 = new HashMap<String, List<Double>>();
			
			final TimeSeries s2 = new TimeSeries("Min Temp");
			//Map<String, List<Double>> cacheMapS2 = new HashMap<String, List<Double>>();
			
			final TimeSeries s3 = new TimeSeries("Room Temp");
			//Map<String, List<Double>> cacheMapS3 = new HashMap<String, List<Double>>();

			for (int i = 0; i < audits.size(); i++) {
				
				Object[] objects = audits.get(i);
				
				Date updateDate = null;
				double defaultTemp = 0;
				double roomTemp = 0;
				double minTemp = 0;
				try{
					int year = (Integer) objects[0];
					int month = (Integer) objects[1];
					int day = (Integer) objects[2];
					int hour = (Integer) objects[3];
					updateDate = calendarService.convertDate(year, month, day, hour);					
					defaultTemp = (Double) objects[4];
					roomTemp = (Double) objects[5];
					minTemp = (Double) objects[6];
				}catch(Exception ex){
					continue;
				}
				
				if(updateDate == null){
					continue;
				}
								
				RegularTimePeriod period = new Hour(updateDate);											
				if(intervalType == ReadMachineDataService.INTERVAL_TYPE_1DAY){
					period = new Day(updateDate);									
				}
														
				s1.addOrUpdate(period, defaultTemp);
										
				s2.addOrUpdate(period, roomTemp);
																					
				s3.addOrUpdate(period, minTemp);
				
			}

			final TimeSeriesCollection dataset = new TimeSeriesCollection();
			dataset.addSeries(s1);
			dataset.addSeries(s2);
			dataset.addSeries(s3);

			StringBuilder sbTimpAxis = new StringBuilder();
			sbTimpAxis.append("Temp Report Axis Report");
			sbTimpAxis.append(" ");
			sbTimpAxis.append(DATA_FORMAT.format(searchFromDate));
			sbTimpAxis.append(" - ");
			sbTimpAxis.append(DATA_FORMAT.format(searchToDate));

			final JFreeChart chart = ChartFactory.createTimeSeriesChart(
					sbTimpAxis.toString(), 
					MessageBundle.getInstance().getMessage("temperature", locale),
					MessageBundle.getInstance().getMessage("time", locale),
					dataset,
					true, true, false);

			final XYPlot plot = chart.getXYPlot();
			plot.getRenderer().setSeriesStroke(0,  new BasicStroke(5.0f));
			plot.getRenderer().setSeriesStroke(1,  new BasicStroke(5.0f));
			plot.getRenderer().setSeriesStroke(2,  new BasicStroke(5.0f));

			// DateAxis domainAxis = (DateAxis) plot.getDomainAxis();
			DateAxis domainAxis = new DateAxis(MessageBundle.getInstance().getMessage("time", locale));
			
			
			DateTickUnit unit = new DateTickUnit(DateTickUnitType.HOUR, 1);
			if(intervalType == ReadMachineDataService.INTERVAL_TYPE_1DAY){
				unit = new DateTickUnit(DateTickUnitType.DAY, 1);
			}else if(intervalType == ReadMachineDataService.INTERVAL_TYPE_6HOUR){
				unit = new DateTickUnit(DateTickUnitType.HOUR, 6);
			}
			
			domainAxis.setTickUnit(unit);
			
			String dataFormat = "(dd)HH:mm";
			if(intervalType == ReadMachineDataService.INTERVAL_TYPE_1DAY){
				dataFormat = "dd/MM";
			}
			

			DateFormat chartFormatter = new SimpleDateFormat(dataFormat);
			domainAxis.setDateFormatOverride(chartFormatter);

			// domainAxis.setDateFormatOverride(new
			// SimpleDateFormat("dd/MM HH"));
			domainAxis.setAutoRange(true);
			plot.setDomainAxis(domainAxis);

			final NumberAxis tempAxis = new NumberAxis(MessageBundle.getInstance().getMessage("temperature", locale));
			tempAxis.setStandardTickUnits(NumberAxis.createIntegerTickUnits());
			tempAxis.setRange(15.0, 35.0);
			tempAxis.setLowerMargin(0.02);
			tempAxis.setUpperMargin(0.02);
			plot.setRangeAxis(tempAxis);

			BufferedImage img = chart.createBufferedImage(1600, // width
					600, // height
					BufferedImage.TYPE_INT_RGB, // image type
					null);

			return img;

		} catch (Exception ex) {
			ex.printStackTrace();
		}
		return null;
	}	
	
	public double calcAvgValue(List<Double> values){
		
		BigDecimal totalValue = new BigDecimal("0");
		BigDecimal size = new BigDecimal(values.size());
		
		Iterator<Double> iter = values.iterator();
		while(iter.hasNext()){
			double value = iter.next();
			totalValue = totalValue.add(new BigDecimal(value));
		}
		
		BigDecimal avg = totalValue.divide(size, 2, BigDecimal.ROUND_HALF_UP);
		
		return avg.doubleValue();
	}
	
	public HSSFWorkbook convertErrorDetailstoExcel(List<ErrorDetail> errorDetails, Locale locale) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		try {
			HSSFSheet sheet = workbook.createSheet();
			int startRow = 0;

			// Row 1
			HSSFRow HeaderRow = sheet.createRow(startRow++);

			// Row 1 - Cell 0
			HSSFCell machineCell0 = HeaderRow.createCell(0);
			machineCell0.setCellValue(MessageBundle.getInstance().getMessage("error_machine_report", locale));
			
			// Row 2
			HSSFRow dateRow = sheet.createRow(startRow++);

			// Row 2 - Cell 0
			HSSFCell dateRow0 = dateRow.createCell(0);
			StringBuilder sbDate = new StringBuilder();
			sbDate.append(MessageBundle.getInstance().getMessage("report_date", locale));
			sbDate.append(": ");
			sbDate.append(DATA_FORMAT.format(new Date()));			
			dateRow0.setCellValue(sbDate.toString());

			// Row 3 - Create Space row
			HSSFRow spaceRow1 = sheet.createRow(startRow++);
			HSSFCell spaceCell0 = spaceRow1.createCell(0);
			spaceCell0.setCellValue("");		

			// Title
			HSSFRow titleRow = sheet.createRow(startRow++);

			HSSFCellStyle titleStyle = workbook.createCellStyle();
			titleStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
			HSSFFont titleFont = workbook.createFont();
			titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
			titleStyle.setFont(titleFont);

			int col = 0;
				
			HSSFCell indexCell = titleRow.createCell(col++);
			indexCell.setCellStyle(titleStyle);			
			indexCell.setCellValue(MessageBundle.getInstance().getMessage("index_logo", locale));

			HSSFCell nidCell = titleRow.createCell(col++);
			nidCell.setCellStyle(titleStyle);
			nidCell.setCellValue(MessageBundle.getInstance().getMessage("nid", locale));
			
			HSSFCell errMsgCell = titleRow.createCell(col++);
			errMsgCell.setCellStyle(titleStyle);
			errMsgCell.setCellValue(MessageBundle.getInstance().getMessage("error_message", locale));
			
			HSSFCell buildingCell = titleRow.createCell(col++);
			buildingCell.setCellStyle(titleStyle);
			buildingCell.setCellValue(MessageBundle.getInstance().getMessage("building_name", locale));
			
			HSSFCell floorCell = titleRow.createCell(col++);
			floorCell.setCellStyle(titleStyle);
			floorCell.setCellValue(MessageBundle.getInstance().getMessage("floor_name", locale));
			
			HSSFCell roomCell = titleRow.createCell(col++);
			roomCell.setCellStyle(titleStyle);
			roomCell.setCellValue(MessageBundle.getInstance().getMessage("room_name", locale));
			
			HSSFCell machineCell = titleRow.createCell(col++);
			machineCell.setCellStyle(titleStyle);
			machineCell.setCellValue(MessageBundle.getInstance().getMessage("machine_name", locale));
		

			int rowSize = startRow;
			int count = 1;
			Iterator<ErrorDetail> iter = errorDetails.iterator();
			
			back :{
				while (iter.hasNext()) {
					ErrorDetail errorDetail = iter.next();
					
					HSSFRow row = sheet.createRow(startRow++);
					
					HSSFCell cell0 = row.createCell(0);
					cell0.setCellValue(String.valueOf(count++));
					
					HSSFCell cell1 = row.createCell(1);
					cell1.setCellValue(String.valueOf(errorDetail.getReadMachineData().getNid()));
					
					HSSFCell cell2 = row.createCell(2);
					if("3".equals(errorDetail.getRealTimeControl().getErrorMessage())){
						cell2.setCellValue(MessageBundle.getInstance().getMessage("error_message_disconnected", locale));
					}else{
						cell2.setCellValue(errorDetail.getRealTimeControl().getErrorMessage());
					}
					
					HSSFCell cell3 = row.createCell(3);
					cell3.setCellValue(errorDetail.getRealTimeControl().getMachine().getRoom().getFloor().getBuilding().getBuildingName());
					
					HSSFCell cell4 = row.createCell(4);
					cell4.setCellValue(errorDetail.getRealTimeControl().getMachine().getRoom().getFloor().getFloorName());
					
					HSSFCell cell5 = row.createCell(5);
					cell5.setCellValue(errorDetail.getRealTimeControl().getMachine().getRoom().getRoomName());
					
					HSSFCell cell6 = row.createCell(6);
					cell6.setCellValue(errorDetail.getRealTimeControl().getMachine().getMachineName());
					
					
					rowSize++;					
					if (rowSize > 60000) {
						HSSFRow endRow = sheet.createRow(rowSize + 2);
						HSSFCell endCell = endRow.createCell(0);
						endCell.setCellValue("Records size are too large....");
						break back;
					}
				}
			}			
			
			
			sheet.setColumnWidth(0, (int) (5 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(1, (int) (15 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(2, (int) (25 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(3, (int) (25 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(4, (int) (25 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(5, (int) (25 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(6, (int) (25 * EXCEL_WIDTH_RATE));					
			
			HSSFPrintSetup printSetup = sheet.getPrintSetup();
			sheet.setAutobreaks(true);

			printSetup.setFitHeight((short) 1);
			printSetup.setFitWidth((short) 1);

		} catch (Exception e) {
			logger.error(e.getMessage());
		}

		return workbook;
	}
	
	public HSSFWorkbook convertIneffectiveMachinestoExcel(List<CommandCheckDisplay> commandCheckDisplays, Locale locale) {
		HSSFWorkbook workbook = new HSSFWorkbook();
		
		try {
			HSSFSheet sheet = workbook.createSheet();
			int startRow = 0;

			// Row 1
			HSSFRow HeaderRow = sheet.createRow(startRow++);

			// Row 1 - Cell 0
			HSSFCell machineCell0 = HeaderRow.createCell(0);
			machineCell0.setCellValue(MessageBundle.getInstance().getMessage("ineffective_machine_report", locale));
			
			// Row 2
			HSSFRow dateRow = sheet.createRow(startRow++);

			// Row 2 - Cell 0
			HSSFCell dateRow0 = dateRow.createCell(0);
			StringBuilder sbDate = new StringBuilder();
			sbDate.append(MessageBundle.getInstance().getMessage("report_date", locale));
			sbDate.append(": ");
			sbDate.append(DATA_FORMAT.format(new Date()));			
			dateRow0.setCellValue(sbDate.toString());

			// Row 3 - Create Space row
			HSSFRow spaceRow1 = sheet.createRow(startRow++);
			HSSFCell spaceCell0 = spaceRow1.createCell(0);
			spaceCell0.setCellValue("");		

			// Title
			HSSFRow titleRow = sheet.createRow(startRow++);

			HSSFCellStyle titleStyle = workbook.createCellStyle();
			titleStyle.setBorderBottom(HSSFCellStyle.BORDER_MEDIUM);
			HSSFFont titleFont = workbook.createFont();
			titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD);
			titleStyle.setFont(titleFont);

			int col = 0;
				
			HSSFCell indexCell = titleRow.createCell(col++);
			indexCell.setCellStyle(titleStyle);			
			indexCell.setCellValue(MessageBundle.getInstance().getMessage("index_logo", locale));

			HSSFCell nidCell = titleRow.createCell(col++);
			nidCell.setCellStyle(titleStyle);
			nidCell.setCellValue(MessageBundle.getInstance().getMessage("nid", locale));
			
			HSSFCell errMsgCell = titleRow.createCell(col++);
			errMsgCell.setCellStyle(titleStyle);
			errMsgCell.setCellValue(MessageBundle.getInstance().getMessage("temperature", locale));
			
			HSSFCell buildingCell = titleRow.createCell(col++);
			buildingCell.setCellStyle(titleStyle);
			buildingCell.setCellValue(MessageBundle.getInstance().getMessage("command_time", locale));
			
			HSSFCell floorCell = titleRow.createCell(col++);
			floorCell.setCellStyle(titleStyle);
			floorCell.setCellValue(MessageBundle.getInstance().getMessage("room_temperature", locale));


			int rowSize = startRow;
			int count = 1;
			Iterator<CommandCheckDisplay> iter = commandCheckDisplays.iterator();
			
			back :{
				while (iter.hasNext()) {
					CommandCheckDisplay commandCheckDisplay = iter.next();
					
					HSSFRow row = sheet.createRow(startRow++);
					
					HSSFCell cell0 = row.createCell(0);
					cell0.setCellValue(String.valueOf(count++));
					
					HSSFCell cell1 = row.createCell(1);
					cell1.setCellValue(String.valueOf(commandCheckDisplay.getNid()));
					
					HSSFCell cell2 = row.createCell(2);
					cell2.setCellValue(commandCheckDisplay.getRealTimeControl().getTemperature());
					
					HSSFCell cell3 = row.createCell(3);
					try{
						cell3.setCellValue(FULL_DATA_FORMAT.format(commandCheckDisplay.getCmdTime()));
					}catch(Exception ex){
						cell3.setCellValue("");
					}
					
					HSSFCell cell4 = row.createCell(4);
					if("E3".equals(commandCheckDisplay.getCurrentCmd())){
						cell4.setCellValue(commandCheckDisplay.getCurrentCmd());
					}else{
						cell4.setCellValue(commandCheckDisplay.getCurrentControl().getRoomTemperature());
					}
									
					
					
					rowSize++;					
					if (rowSize > 60000) {
						HSSFRow endRow = sheet.createRow(rowSize + 2);
						HSSFCell endCell = endRow.createCell(0);
						endCell.setCellValue("Records size are too large....");
						break back;
					}
				}
			}			
			
			
			sheet.setColumnWidth(0, (int) (5 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(1, (int) (15 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(2, (int) (25 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(3, (int) (25 * EXCEL_WIDTH_RATE));
			sheet.setColumnWidth(4, (int) (25 * EXCEL_WIDTH_RATE));					
			
			HSSFPrintSetup printSetup = sheet.getPrintSetup();
			sheet.setAutobreaks(true);

			printSetup.setFitHeight((short) 1);
			printSetup.setFitWidth((short) 1);

		} catch (Exception e) {
			logger.error(e.getMessage());
		}

		return workbook;
	}
}
